package com.example.demo;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import lombok.extern.slf4j.Slf4j;

/**
 * @author jiangjinwu
 * @version 1.0
 * @since 2025/10/1 21:03
 */

@Slf4j

public class AchievementDataListener implements ReadListener<AchievementStats> {
  private static final int BATCH_SIZE = 1000;
  private List<AchievementStats> list = new ArrayList<>(BATCH_SIZE);
  BigDecimal archThreshold = new BigDecimal("300000");
  BigDecimal archThresholdHigh = new BigDecimal("30000000");

  @Override
  public void invoke(AchievementStats data, AnalysisContext context) {
    list.add(data);
    if (list.size() >= BATCH_SIZE) {
      generateSql();
      list.clear();
    }
  }

  @Override
  public void doAfterAllAnalysed(AnalysisContext context) {
    if (!list.isEmpty()) {
      generateSql();
    }
  }


  /**
   * SQL 转义单引号
   */
  private String escapeSql(String str) {
    if (str == null) return "";
    return str.replace("'", "''");
  }

  private Integer getTodayIntValue(){
    LocalDate date = LocalDate.now();
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
    String dateStr = date.format(formatter);
    return Integer.parseInt(dateStr);
  }

  public   void generateSql( ) {
    String CURRENT_DATE = "'" + LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) + "'";
    var filePath = "statsql123.sql";
    Long tenantId = 123L;
    try (OutputStreamWriter writer = new OutputStreamWriter(
        new FileOutputStream(filePath,true), StandardCharsets.UTF_8)) {

      for (int start = 0; start < list.size(); start += BATCH_SIZE) {
        int end = Math.min(start + BATCH_SIZE, list.size());
        List<AchievementStats> batchList = list.subList(start, end);

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO t_achievement_stats_123 " +
            "(user_no, nick, mobile, achievement, day, tenant_id) VALUES ");

        for (int i = 0; i < batchList.size(); i++) {

          AchievementStats row = batchList.get(i);
          BigDecimal achievement = null;
          try {
            if (row.getAchievement() != null && !row.getAchievement().trim().isEmpty()) {
              achievement = new BigDecimal(row.getAchievement().replaceAll(",", "").trim());
            }
          } catch (Exception e) {
            System.err.println("转换成就数值失败: " + row.getAchievement() + " - " + e.getMessage());
          }

          if(ObjectUtil.compare(achievement, archThreshold)>0) {
            sql.append("('");
            sql.append(escapeSql(row.getUserNo())).append("', '");
            sql.append(escapeSql(row.getNick())).append("', '");
            sql.append(escapeSql(row.getMobile())).append("', ")
                .append(achievement != null ? achievement.toPlainString() : "NULL").append(", ")
                .append(CURRENT_DATE).append(", ")
                .append(tenantId).append(")");

            if (i != batchList.size() - 1) {
              sql.append(",");
            }
          }
          }

        sql.append(";\n"); // 每条批量 SQL 单独一行

        // 写文件
        writer.write(sql.toString());
      }

      System.out.println("SQL 文件生成完成: " + filePath);
      System.out.println("总记录数: " + list.size());

    } catch (IOException e) {
      throw new RuntimeException("生成 SQL 文件失败", e);
    }
  }


  public   void generateSql2( ) {
    String CURRENT_DATE = "'" + LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) + "'";
    var filePath = "12333.sql";
    Long tenantId = 123L;
    try (OutputStreamWriter writer = new OutputStreamWriter(
        new FileOutputStream(filePath,true), StandardCharsets.UTF_8)) {

      for (int start = 0; start < list.size(); start += BATCH_SIZE) {
        int end = Math.min(start + BATCH_SIZE, list.size());
        List<AchievementStats> batchList = list.subList(start, end);

        StringBuilder sql = new StringBuilder();
        sql.append("");

        for (int i = 0; i < batchList.size(); i++) {

          AchievementStats row = batchList.get(i);
          BigDecimal achievement = null;
          try {
            if (row.getAchievement() != null && !row.getAchievement().trim().isEmpty()) {
              achievement = new BigDecimal(row.getAchievement().replaceAll(",", "").trim());
            }
          } catch (Exception e) {
            System.err.println("转换成就数值失败: " + row.getAchievement() + " - " + e.getMessage());
          }

//          if(ObjectUtil.compare(achievement, archThreshold)>0
////              && ObjectUtil.compare(achievement, archThresholdHigh)<0
//
//          ) {
          sql.append("('")
              .append(escapeSql(row.getUserNo())).append("', '")
              .append(escapeSql(row.getNick())).append("', '")
              .append(escapeSql(row.getMobile())).append("', ")
              .append(achievement != null ? achievement.toPlainString() : "NULL").append(", ")
              .append(CURRENT_DATE).append(", ")
              .append(tenantId).append(")");

          if (i != batchList.size() - 1) {
            sql.append(",");
          }
        }
//        }
        sql.append(";\n"); // 每条批量 SQL 单独一行

        // 写文件
        writer.write(sql.toString());
      }

      System.out.println("SQL 文件生成完成: " + filePath);
      System.out.println("总记录数: " + list.size());

    } catch (IOException e) {
      throw new RuntimeException("生成 SQL 文件失败", e);
    }
  }
}
